


--use PolePosition

-- // checksum failed custom error
--exec sp_addmessage @msgnum = 505050, @severity = 16, @msgtext = N'%s(%s) checksum compare failed', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // not found custom error
--exec sp_addmessage @msgnum = 505060, @severity = 16, @msgtext = N'%s(%s) does not exist', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // unit test custom error
--exec sp_addmessage @msgnum = 505070, @severity = 16, @msgtext = N'%s Test Failed. Expected< %s > Actual< %s > %s', @replace = 'REPLACE', @lang = 'us_english'
GO



--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'v_tb_Utility_Polygons') and OBJECTPROPERTY(id, N'IsProcedure') = 0)
drop view v_tb_Utility_Polygons
GO

create view v_tb_Utility_Polygons as
	/* 	Initial Generated Date:  Saturday, February 14, 2009 
		Purpose: Selected record(s) from the table; this view is the source for all procedures' select statements
	*/
	
select
	 [Utility_PolygonId] as [Utility_PolygonId]
	,[Name] as [Name]
	,[Altitude] as [Altitude]
	,[North] as [North]
	,[East] as [East]
	,[South] as [South]
	,[West] as [West]
	,[Utility_Polygon_ParentPolygonFk] as [Utility_Polygon_ParentPolygonFk]
	,[Utility_Polygon_PolygonTypeFk] as [Utility_Polygon_PolygonTypeFk]
	,[LineColor] as [LineColor]
	,[LineWidth] as [LineWidth]
	,[FillColor] as [FillColor]
	,[FillOpacity] as [FillOpacity]
	,[FontColor] as [FontColor]
	,[Font] as [Font]
	,[UtilityFk] as [UtilityFk]
	-- customization may be required for FriendlyName
	,Left(CONVERT(varchar(255), [Name]), 255) as [FriendlyName]
from
	tb_Utility_Polygons
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityPolygon_Test') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityPolygon_Test
GO


create proc up_UtilityPolygon_Test
	@Utility_PolygonId int
as
	/* 	Initial Generated Date:  Saturday, February 14, 2009 
		Purpose: Test if a record exists, using the primary key
	*/

SET NOCOUNT ON	
	
if exists(
	select [Utility_PolygonId] 
	from [tb_Utility_Polygons] source
	where [source].[Utility_PolygonId] = @Utility_PolygonId)
begin
	return(1)
end
else
begin
	return(0)
end
GO






--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityPolygon_Select') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityPolygon_Select
GO

create proc up_UtilityPolygon_Select
	 @Utility_PolygonId int = null 
	,@Name nvarchar(100) = null 
	,@Altitude float = null 
	,@North float = null 
	,@East float = null 
	,@South float = null 
	,@West float = null 
	,@Utility_Polygon_ParentPolygonFk int = null 
	,@Utility_Polygon_PolygonTypeFk int = null 
	,@LineColor int = null 
	,@LineWidth int = null 
	,@FillColor int = null 
	,@FillOpacity int = null 
	,@FontColor int = null 
	,@Font nvarchar(100) = null 
	,@UtilityFk int = null 
as
	/* 	Initial Generated Date:  Saturday, February 14, 2009 
		Purpose: Selected record(s) based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select * from v_tb_Utility_Polygons
where
		IsNull([v_tb_Utility_Polygons].[Utility_PolygonId], -1) = Coalesce(@Utility_PolygonId, [v_tb_Utility_Polygons].[Utility_PolygonId], -1)
	AND	IsNull([v_tb_Utility_Polygons].[Name], '') = Coalesce(@Name, [v_tb_Utility_Polygons].[Name], '')
	AND	IsNull([v_tb_Utility_Polygons].[Altitude], -1) = Coalesce(@Altitude, [v_tb_Utility_Polygons].[Altitude], -1)
	AND	IsNull([v_tb_Utility_Polygons].[North], -1) = Coalesce(@North, [v_tb_Utility_Polygons].[North], -1)
	AND	IsNull([v_tb_Utility_Polygons].[East], -1) = Coalesce(@East, [v_tb_Utility_Polygons].[East], -1)
	AND	IsNull([v_tb_Utility_Polygons].[South], -1) = Coalesce(@South, [v_tb_Utility_Polygons].[South], -1)
	AND	IsNull([v_tb_Utility_Polygons].[West], -1) = Coalesce(@West, [v_tb_Utility_Polygons].[West], -1)
	AND	IsNull([v_tb_Utility_Polygons].[Utility_Polygon_ParentPolygonFk], -1) = Coalesce(@Utility_Polygon_ParentPolygonFk, [v_tb_Utility_Polygons].[Utility_Polygon_ParentPolygonFk], -1)
	AND	IsNull([v_tb_Utility_Polygons].[Utility_Polygon_PolygonTypeFk], -1) = Coalesce(@Utility_Polygon_PolygonTypeFk, [v_tb_Utility_Polygons].[Utility_Polygon_PolygonTypeFk], -1)
	AND	IsNull([v_tb_Utility_Polygons].[LineColor], -1) = Coalesce(@LineColor, [v_tb_Utility_Polygons].[LineColor], -1)
	AND	IsNull([v_tb_Utility_Polygons].[LineWidth], -1) = Coalesce(@LineWidth, [v_tb_Utility_Polygons].[LineWidth], -1)
	AND	IsNull([v_tb_Utility_Polygons].[FillColor], -1) = Coalesce(@FillColor, [v_tb_Utility_Polygons].[FillColor], -1)
	AND	IsNull([v_tb_Utility_Polygons].[FillOpacity], -1) = Coalesce(@FillOpacity, [v_tb_Utility_Polygons].[FillOpacity], -1)
	AND	IsNull([v_tb_Utility_Polygons].[FontColor], -1) = Coalesce(@FontColor, [v_tb_Utility_Polygons].[FontColor], -1)
	AND	IsNull([v_tb_Utility_Polygons].[Font], '') = Coalesce(@Font, [v_tb_Utility_Polygons].[Font], '')
	AND	IsNull([v_tb_Utility_Polygons].[UtilityFk], -1) = Coalesce(@UtilityFk, [v_tb_Utility_Polygons].[UtilityFk], -1)
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityPolygon_Select_Top1') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityPolygon_Select_Top1
GO

create proc up_UtilityPolygon_Select_Top1
	 @Utility_PolygonId int = null 
	,@Name nvarchar(100) = null 
	,@Altitude float = null 
	,@North float = null 
	,@East float = null 
	,@South float = null 
	,@West float = null 
	,@Utility_Polygon_ParentPolygonFk int = null 
	,@Utility_Polygon_PolygonTypeFk int = null 
	,@LineColor int = null 
	,@LineWidth int = null 
	,@FillColor int = null 
	,@FillOpacity int = null 
	,@FontColor int = null 
	,@Font nvarchar(100) = null 
	,@UtilityFk int = null 
as
	/* 	Initial Generated Date:  Saturday, February 14, 2009 
		Purpose: Selected top 1 record based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select TOP 1 * from v_tb_Utility_Polygons
where
		IsNull([v_tb_Utility_Polygons].[Utility_PolygonId], -1) = Coalesce(@Utility_PolygonId, [v_tb_Utility_Polygons].[Utility_PolygonId], -1)
	AND	IsNull([v_tb_Utility_Polygons].[Name], '') = Coalesce(@Name, [v_tb_Utility_Polygons].[Name], '')
	AND	IsNull([v_tb_Utility_Polygons].[Altitude], -1) = Coalesce(@Altitude, [v_tb_Utility_Polygons].[Altitude], -1)
	AND	IsNull([v_tb_Utility_Polygons].[North], -1) = Coalesce(@North, [v_tb_Utility_Polygons].[North], -1)
	AND	IsNull([v_tb_Utility_Polygons].[East], -1) = Coalesce(@East, [v_tb_Utility_Polygons].[East], -1)
	AND	IsNull([v_tb_Utility_Polygons].[South], -1) = Coalesce(@South, [v_tb_Utility_Polygons].[South], -1)
	AND	IsNull([v_tb_Utility_Polygons].[West], -1) = Coalesce(@West, [v_tb_Utility_Polygons].[West], -1)
	AND	IsNull([v_tb_Utility_Polygons].[Utility_Polygon_ParentPolygonFk], -1) = Coalesce(@Utility_Polygon_ParentPolygonFk, [v_tb_Utility_Polygons].[Utility_Polygon_ParentPolygonFk], -1)
	AND	IsNull([v_tb_Utility_Polygons].[Utility_Polygon_PolygonTypeFk], -1) = Coalesce(@Utility_Polygon_PolygonTypeFk, [v_tb_Utility_Polygons].[Utility_Polygon_PolygonTypeFk], -1)
	AND	IsNull([v_tb_Utility_Polygons].[LineColor], -1) = Coalesce(@LineColor, [v_tb_Utility_Polygons].[LineColor], -1)
	AND	IsNull([v_tb_Utility_Polygons].[LineWidth], -1) = Coalesce(@LineWidth, [v_tb_Utility_Polygons].[LineWidth], -1)
	AND	IsNull([v_tb_Utility_Polygons].[FillColor], -1) = Coalesce(@FillColor, [v_tb_Utility_Polygons].[FillColor], -1)
	AND	IsNull([v_tb_Utility_Polygons].[FillOpacity], -1) = Coalesce(@FillOpacity, [v_tb_Utility_Polygons].[FillOpacity], -1)
	AND	IsNull([v_tb_Utility_Polygons].[FontColor], -1) = Coalesce(@FontColor, [v_tb_Utility_Polygons].[FontColor], -1)
	AND	IsNull([v_tb_Utility_Polygons].[Font], '') = Coalesce(@Font, [v_tb_Utility_Polygons].[Font], '')
	AND	IsNull([v_tb_Utility_Polygons].[UtilityFk], -1) = Coalesce(@UtilityFk, [v_tb_Utility_Polygons].[UtilityFk], -1)
GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityPolygon_Insert') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityPolygon_Insert
GO

create proc up_UtilityPolygon_Insert
	 @Utility_PolygonId int =null OUTPUT  
	,@Name nvarchar(100)  
	,@Altitude float  
	,@North float  
	,@East float  
	,@South float  
	,@West float  
	,@Utility_Polygon_ParentPolygonFk int = null  
	,@Utility_Polygon_PolygonTypeFk int  
	,@LineColor int = null  
	,@LineWidth int = null  
	,@FillColor int = null  
	,@FillOpacity int = null  
	,@FontColor int = null  
	,@Font nvarchar(100) = null  
	,@UtilityFk int  
as
	/* 	Initial Generated Date:  Saturday, February 14, 2009 
		Purpose: Inserts a new record; missing arguments are set as their default (typically null)
	*/

SET NOCOUNT ON	
--int

insert tb_Utility_Polygons 
	(
	
	 [Name] 
	,[Altitude] 
	,[North] 
	,[East] 
	,[South] 
	,[West] 
	,[Utility_Polygon_ParentPolygonFk] 
	,[Utility_Polygon_PolygonTypeFk] 
	,[LineColor] 
	,[LineWidth] 
	,[FillColor] 
	,[FillOpacity] 
	,[FontColor] 
	,[Font] 
	,[UtilityFk] 
	)
	values
	(
	
	 @Name 
	,@Altitude 
	,@North 
	,@East 
	,@South 
	,@West 
	,@Utility_Polygon_ParentPolygonFk 
	,@Utility_Polygon_PolygonTypeFk 
	,@LineColor 
	,@LineWidth 
	,@FillColor 
	,@FillOpacity 
	,@FontColor 
	,@Font 
	,@UtilityFk 
	)
set @Utility_PolygonId = scope_identity()
select * from v_tb_Utility_Polygons where Utility_PolygonId = @Utility_PolygonId

GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityPolygon_Update') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityPolygon_Update
GO


create proc up_UtilityPolygon_Update
	 @Utility_PolygonId int  
	,@Name nvarchar(100) = null  
	,@Altitude float = null  
	,@North float = null  
	,@East float = null  
	,@South float = null  
	,@West float = null  
	,@Utility_Polygon_ParentPolygonFk int = null  
	,@Utility_Polygon_PolygonTypeFk int = null  
	,@LineColor int = null  
	,@LineWidth int = null  
	,@FillColor int = null  
	,@FillOpacity int = null  
	,@FontColor int = null  
	,@Font nvarchar(100) = null  
	,@UtilityFk int = null  
	,@OriginalUtility_PolygonId int  = null --Original PrimaryKey
	
as
	/* 	Initial Generated Date:  Saturday, February 14, 2009 
		Purpose: Updates an existing record, verifying exist; missing arguments persist original record value
		NOTE: this version of the update allows up to write nulls to the table.
	*/

SET NOCOUNT ON	

if @OriginalUtility_PolygonId is null
	set @OriginalUtility_PolygonId = @Utility_PolygonId

-- test
declare @Result bit
execute @Result = up_UtilityPolygon_Test @OriginalUtility_PolygonId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Utility_PolygonId)
	raiserror(505060, 16, 1, 'UtilityPolygon', @x)
	return
end

-- proceed
update tb_Utility_Polygons 
set

	 [Name] = IsNull(@Name, [source].[Name])
	,[Altitude] = IsNull(@Altitude, [source].[Altitude])
	,[North] = IsNull(@North, [source].[North])
	,[East] = IsNull(@East, [source].[East])
	,[South] = IsNull(@South, [source].[South])
	,[West] = IsNull(@West, [source].[West])
	,[Utility_Polygon_ParentPolygonFk] =  @Utility_Polygon_ParentPolygonFk
	,[Utility_Polygon_PolygonTypeFk] = IsNull(@Utility_Polygon_PolygonTypeFk, [source].[Utility_Polygon_PolygonTypeFk])
	,[LineColor] =  @LineColor
	,[LineWidth] =  @LineWidth
	,[FillColor] =  @FillColor
	,[FillOpacity] =  @FillOpacity
	,[FontColor] =  @FontColor
	,[Font] =  @Font
	,[UtilityFk] = IsNull(@UtilityFk, [source].[UtilityFk])
	
from tb_Utility_Polygons source
where
	[source].[Utility_PolygonId] = @OriginalUtility_PolygonId


select * from v_tb_Utility_Polygons where Utility_PolygonId = @Utility_PolygonId
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_UtilityPolygon_Delete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_UtilityPolygon_Delete
GO


create proc up_UtilityPolygon_Delete
	@Utility_PolygonId int
as
	/* 	Initial Generated Date:  Saturday, February 14, 2009 
		Purpose: Deletes an existing record, verifying exist
	*/

SET NOCOUNT ON	

-- test
declare @Result bit
execute @Result = up_UtilityPolygon_Test @Utility_PolygonId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @Utility_PolygonId)
	raiserror(505060, 16, 1, 'UtilityPolygon', @x)
	return
end

-- proceed
delete from tb_Utility_Polygons 
where [Utility_PolygonId] = @Utility_PolygonId

GO


